USE [DWH_REPORT]
GO

/****** Object:  StoredProcedure [dbo].[BaoCaoGiaiNgan2]    Script Date: 08/04/2012 18:21:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[BaoCaoGiaiNgan2]
	@CURRENCY nvarchar(3) ='All',
	@loaiKH int =0,
	@LDLINK nvarchar(200) ='',
	@MaKH nvarchar(200) ='',
	@dateFrom nvarchar(30),
	@dateTo nvarchar(30)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- Insert statements for procedure here

			select distinct LD.CURRENCY,LD.TERM,[LOAN.TRANSACTION].[REPORT.DATE],
				LD.[FIN.MAT.DATE],LD.CATEGORY,LD.[LD.LINK],LD.[LD.ID],[LOAN.TRANSACTION].[TRANS.AMOUNT],
				LD.[INTEREST.RATE],CUSTOMER.[SECTOR.ID],CUSTOMER.NAME,CUSTOMER.[CUSTOMER.ID]
				
			from LD,CUSTOMER,[LOAN.TRANSACTION]
			where
				LD.[CUSTOMER.ID]=CUSTOMER.[CUSTOMER.ID] 
				and [LOAN.TRANSACTION].[LD.ID]=LD.[LD.ID] 
				and LD.[REPORT.DATE]=LD.[VALUE.DATE]
				and LD.[CATEGORY.ID]>21050 and LD.[CATEGORY.ID]<21073 and LD.[CATEGORY.ID]!=21059 
				and LD.[VALUE.DATE]<>[LOAN.TRANSACTION].[REPORT.DATE]
				and [LOAN.TRANSACTION].[TRANS.TYPE]='DI'
				and LD.[VALUE.DATE]>=@dateFrom
				and LD.[VALUE.DATE]<=@dateTo
				and [LOAN.TRANSACTION].[REPORT.DATE]>=@dateFrom
				and [LOAN.TRANSACTION].[REPORT.DATE]<=@dateTo
				and LD.CURRENCY=
					case @CURRENCY
						when 'All' then LD.CURRENCY
						else @CURRENCY
					end
				and ((@loaiKH=0 and CUSTOMER.[SECTOR.ID]=CUSTOMER.[SECTOR.ID]) or (@loaiKH=1 and CUSTOMER.[SECTOR.ID]<=1002)
				or (@loaiKH=2 and CUSTOMER.[SECTOR.ID]>1002))
				and LD.[LD.LINK]=
					case @LDLINK
						when '' then LD.[LD.LINK]
						else @LDLINK
					end
				and LD.[CUSTOMER.ID]=
					case @MaKH
						when '' then LD.[CUSTOMER.ID]
						else @MaKH
					end	
END
GO

